package com.zerowidth.db.op;

import com.zerowidth.db.DBHelper;
import com.zerowidth.db.base.SqlResult;
import com.zerowidth.db.bean.AlbumRecordBean;
import com.zerowidth.db.bean.vm.DaySimpleVM;
import com.zerowidth.db.utils.IOUtils;
import com.zerowidth.db.utils.StringUtils;
import com.zerowidth.db.utils.TimeUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class AlbumRecordDB {

    public static AlbumRecordBean insert(AlbumRecordBean record) {
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO album_record (user_id, baby_id, day, bucket, object_key, ETag, file_type, " +
                        "video_duration, create_time) " +
                        "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, '%s')",
                record.user_id, record.baby_id, record.day, record.bucket, record.object_key, record.etag,
                record.file_type, record.video_duration, time);
        record.id = DBHelper.executeInsert(sql);
        return record;
    }

    /**
     * @param userId
     * @param babyId
     * @param day    查询此日期之前的照片数据，用于分页；格式如: 2020-06-10
     * @param count  每天的样式展示条数，如：列表中每天显示前5张照片
     * @return
     */
    public static Collection<DaySimpleVM> getAlbumPageList(String userId, String babyId, String day, int count) {

        //临时存储结果集。 按天分组，存储每一天的数据
        Map<String, DaySimpleVM> map = new TreeMap<>(new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                return -o1.compareTo(o2); //大值 排在前面
            }
        });
        String startDay = null; //最晚的日期 ，注意排序，从新到旧 取照片视频
        String deadDay = null;  //最早的日期

        {
            //第一步，查询最近的10天的日期
            int daysInPage = 10; //每页显示的天数

            String sqlForDeadLine;
            if (StringUtils.isEmpty(day)) {
                sqlForDeadLine = String.format("SELECT day FROM " +
                        "(SELECT day FROM album_record WHERE delete_flag=0 GROUP BY day ORDER BY day DESC) temp " +
                        "LIMIT %d", daysInPage);
            } else {
                sqlForDeadLine = String.format("SELECT day FROM " +
                        "(SELECT day FROM album_record WHERE delete_flag=0 GROUP BY day ORDER BY day DESC) temp " +
                        "WHERE day < '%s' LIMIT %d", day, daysInPage);
            }
            ResultSet rsDeadLine = DBHelper.executeQuery(sqlForDeadLine);
            if (rsDeadLine == null) {
                return null;
            }

            try {
                if (rsDeadLine.first()) {
                    startDay = rsDeadLine.getString("day");
                }
                if (rsDeadLine.last()) {
                    deadDay = rsDeadLine.getString("day");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                IOUtils.close(rsDeadLine);
            }

            if (StringUtils.isEmpty(startDay)) {
                return null;
            }
        }

        {
            //第二步，取出每一天的 最新上传的 前6个图片视频
            if (count == 0) {
                count = 6;//默认每天在列表中显示6张照片
            }

            String sql = String.format("select * from album_record a " +
                            "where delete_flag = 0 and day <= '%s' and day >= '%s' and baby_id = '%s' and %d>(select count(*) " +
                            "from album_record where delete_flag = 0 and day=a.day and create_time>a.create_time and baby_id = '%s') " +
                            "order by a.day desc,a.id desc",
                    startDay, deadDay, babyId, count, babyId);

            ResultSet rs = DBHelper.executeQuery(sql);

            if (rs != null) {
                try {
                    while (rs.next()) {
                        AlbumRecordBean albumRecordBean = new AlbumRecordBean();
                        albumRecordBean.id = rs.getInt("id");
                        albumRecordBean.user_id = rs.getString("user_id");
                        albumRecordBean.baby_id = rs.getString("baby_id");
                        String dayStr = albumRecordBean.day = rs.getString("day");
                        albumRecordBean.bucket = rs.getString("bucket");
                        albumRecordBean.object_key = rs.getString("object_key");
                        albumRecordBean.etag = rs.getString("ETag");
                        albumRecordBean.file_type = rs.getString("file_type");
                        albumRecordBean.create_time = rs.getString("create_time");
                        albumRecordBean.video_duration = rs.getLong("video_duration");
                        albumRecordBean.delete_flag = rs.getBoolean("delete_flag");
                        albumRecordBean.delete_time = rs.getString("delete_time");

                        if (map.get(dayStr) == null) {
                            map.put(dayStr, new DaySimpleVM(dayStr));
                        }
                        map.get(albumRecordBean.day).album_record_bean_list.add(albumRecordBean);
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                } finally {
                    IOUtils.close(rs);
                }
            }
        }

        {
            //第三步，统计每一天的 图片和视频的数量
            String sqlCount = String.format("select day, file_type, count(*) count from album_record " +
                    "where baby_id = '%s' and delete_flag = 0 " +
                    "and day >= '%s' and day <= '%s' " +
                    "group by day, file_type", babyId, deadDay, startDay);
            ResultSet rsCount = DBHelper.executeQuery(sqlCount);
            if (rsCount != null) {
                try {
                    while (rsCount.next()) {
                        String day0 = rsCount.getString("day");
                        if (map.get(day0) == null) {
                            map.put(day0, new DaySimpleVM(day0));
                        }

                        String fileType = rsCount.getString("file_type");
                        if (StringUtils.equals(fileType, "image")) {
                            map.get(day0).image_count = rsCount.getInt("count");
                        } else if (StringUtils.equals(fileType, "video")) {
                            map.get(day0).video_count = rsCount.getInt("count");
                        }
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return map.values();
    }

    public static List<AlbumRecordBean> getDayAlbumPageList(
            String babyId, String day, String record_id, int count) {

        String sql = "";
        if (StringUtils.isEmpty(record_id)) {
            sql = String.format("SELECT * FROM album_record WHERE delete_flag = 0 and baby_id = '%s' AND day = '%s' " +
                    "ORDER BY id DESC LIMIT %d", babyId, day, count);
        } else {
            sql = String.format("SELECT * FROM album_record WHERE delete_flag = 0 and baby_id = '%s' AND day = '%s' " +
                    " AND id < '%s' ORDER BY id DESC LIMIT %d", babyId, day, record_id, count);
        }

        List<AlbumRecordBean> recordBeanList = new ArrayList<>();
        SqlResult sr = DBHelper.executeQueryEx(sql);

        if (sr == null) {
            return recordBeanList;
        }
        if (sr.rs == null) {
            return recordBeanList;
        }

        try {
            while (sr.rs.next()) {
                AlbumRecordBean bean = new AlbumRecordBean();
                bean.id = sr.rs.getInt("id");
                bean.user_id = sr.rs.getString("user_id");
                bean.baby_id = sr.rs.getString("baby_id");
                bean.day = sr.rs.getString("day");
                bean.bucket = sr.rs.getString("bucket");
                bean.object_key = sr.rs.getString("object_key");
                bean.etag = sr.rs.getString("ETag");
                bean.file_type = sr.rs.getString("file_type");
                bean.create_time = sr.rs.getString("create_time");
                bean.video_duration = sr.rs.getInt("video_duration");
                bean.delete_flag = sr.rs.getBoolean("delete_flag");
                bean.delete_time = sr.rs.getString("delete_time");

                recordBeanList.add(bean);
            }
        } catch (Exception e) {

        }
        sr.close();

        return recordBeanList;
    }

    public static AlbumRecordBean findAlbumRecord(String id) {
        String sql = String.format("SELECT * FROM album_record WHERE id = '%s' ", id);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        if (sr == null || sr.rs == null) {
            return null;
        }

        try {
            if (sr.rs.next()) {
                AlbumRecordBean bean = new AlbumRecordBean();
                bean.id = sr.rs.getInt("id");
                bean.user_id = sr.rs.getString("user_id");
                bean.baby_id = sr.rs.getString("baby_id");
                bean.day = sr.rs.getString("day");
                bean.bucket = sr.rs.getString("bucket");
                bean.object_key = sr.rs.getString("object_key");
                bean.etag = sr.rs.getString("ETag");
                bean.file_type = sr.rs.getString("file_type");
                bean.create_time = sr.rs.getString("create_time");
                bean.video_duration = sr.rs.getInt("video_duration");
                bean.delete_flag = sr.rs.getBoolean("delete_flag");
                bean.delete_time = sr.rs.getString("delete_time");

                return bean;
            }
        } catch (Exception e) {

        }
        sr.close();

        return null;
    }

    public static boolean deleteAlbumRecord(String id) {
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("UPDATE album_record SET delete_flag = true, " +
                "delete_time = '%s' WHERE id = '%s' ", time, id);

        return DBHelper.executeUpdate(sql) > 0;
    }


}
